﻿using System;
using Microsoft.TeamFoundation.Client;
using Microsoft.TeamFoundation.WorkItemTracking.Client;
using System.Text;
using WorkItemGrabber.Properties;
using Microsoft.Practices.EnterpriseLibrary.Common;
using Microsoft.Practices.EnterpriseLibrary.Data;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;
using System.Net;
namespace WorkItemGrabber
{
    /// <summary>
    /// 
    /// </summary>
    class QueryFolderSample
    {

        static void Main(string[] args)
        {
            string strException = string.Empty;
            DateTime LastRunTime = DateTime.Now;
            try
            {
                Console.WriteLine(GetLastRunDate());
                TfsTeamProjectCollection coll = TfsTeamProjectCollectionFactory.GetTeamProjectCollection(new Uri(Settings.Default.tfsURL), new MyIdentity());

                coll.EnsureAuthenticated();

                WorkItemStore store = new WorkItemStore(coll);

                // Query oQuery = new Query(store, "SELECT [System.ID],[System.Title],[System.CreatedDate],[System.AssignedTo],[System.Description],[System.ChangedDate],[System.State] FROM WorkItems WHERE [System.WorkItemType]='Teleco_Requirement' and [System.TeamProject]='" + Settings.Default.tfsProjectName + "'");
               // Query oQuery = new Query(store, "SELECT [System.ID],[System.Title],[System.CreatedDate],[System.AssignedTo],[System.Description],[System.ChangedDate],[System.State] FROM WorkItems WHERE [System.TeamProject]='" + Settings.Default.tfsProjectName + "' and [System.ChangedDate]>='"+GetLastRunDate()+"'");
                Query oQuery = new Query(store, "SELECT [System.ID],[System.Title],[System.CreatedDate],[System.AssignedTo],[System.Description],[System.ChangedDate],[System.State] FROM WorkItems WHERE [System.ChangedDate]>='" + GetLastRunDate() + "'");


                WorkItemCollection oWorkItemCollection = oQuery.RunQuery();

                for (int i = 0; i < oWorkItemCollection.Count; i++)
                {
                    UpdateWorkItem(oWorkItemCollection[i]);
                }
                HouseKeeping();
            }
            catch (Exception ex)
            {
                strException=ex.Message;
            }
            SaveLastRunDate(LastRunTime, strException);
        }

        /// <summary>
        /// Updates the work item.
        /// </summary>
        /// <param name="oWorkitem">The o workitem.</param>
        private static void UpdateWorkItem(WorkItem oWorkitem)
        {
            try
            {
                string Description = "<br />Title:" + oWorkitem.Title + "<br />";
                Description += "Description" + oWorkitem.Description.Replace("\n", "<br />");
                Console.WriteLine(oWorkitem.Id);

                Database db = DatabaseFactory.CreateDatabase();

                DbCommand dbCommand = db.GetSqlStringCommand("WorkItemInsert");
                dbCommand.CommandType = CommandType.StoredProcedure;
                db.AddInParameter(dbCommand, "ID", DbType.Int32, oWorkitem.Id);
                db.AddInParameter(dbCommand, "Title", DbType.String, oWorkitem.Title);
                db.AddInParameter(dbCommand, "CreatedDate", DbType.DateTime, oWorkitem.CreatedDate);
                db.AddInParameter(dbCommand, "Rev", DbType.Int32, oWorkitem.Rev);
                db.AddInParameter(dbCommand, "CreatedBy", DbType.String, oWorkitem.CreatedBy);
                db.AddInParameter(dbCommand, "ChangedBy", DbType.String, oWorkitem.ChangedBy);
                db.AddInParameter(dbCommand, "State", DbType.String, oWorkitem.State);
                db.AddInParameter(dbCommand, "ChangedDate", DbType.DateTime, oWorkitem.ChangedDate);
                db.AddInParameter(dbCommand, "AssignedTo", DbType.String, oWorkitem.Fields["System.AssignedTo"].Value.ToString());
                db.AddInParameter(dbCommand, "Description", DbType.String, Description);
                db.AddInParameter(dbCommand, "Type", DbType.String, oWorkitem.Type.Name);
                db.AddInParameter(dbCommand, "tfsProjectName", DbType.String, oWorkitem.Project.Name);
                db.ExecuteNonQuery(dbCommand);
            }
            catch { }
        }
        /// <summary>
        /// to mark old items as deleted
        /// </summary>
        private static void HouseKeeping()
        {

            Database db = DatabaseFactory.CreateDatabase();

            DbCommand dbCommand = db.GetSqlStringCommand("WorkItemHouseKeeping");
            dbCommand.CommandType = CommandType.StoredProcedure;

            db.ExecuteNonQuery(dbCommand);

        }

        /// <summary>
        /// to mark old items as deleted
        /// </summary>
        private static string GetLastRunDate()
        {

            Database db = DatabaseFactory.CreateDatabase();

            DbCommand dbCommand = db.GetSqlStringCommand("ToolLastRunTimeSelect");
            dbCommand.CommandType = CommandType.StoredProcedure;

            return ((DateTime)db.ExecuteScalar(dbCommand)).ToShortDateString();
        }
        /// <summary>
        /// to mark old items as deleted
        /// </summary>
        private static void SaveLastRunDate(DateTime LastRunTime,string strException)
        {

            Database db = DatabaseFactory.CreateDatabase();

            DbCommand dbCommand = db.GetSqlStringCommand("ToolLastRunTimeInsert");
            dbCommand.CommandType = CommandType.StoredProcedure;

            db.AddInParameter(dbCommand, "last_run_time", DbType.DateTime, LastRunTime);
            db.AddInParameter(dbCommand, "Exception", DbType.String, strException);
            db.ExecuteNonQuery(dbCommand);
        }
    }
}
